/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import Model.Customer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
/**
 *
 * @author Radu
 */
public class DbCustomer implements DbInterfaceCustomer {
    private Connection db_con;

    public DbCustomer(){
        db_con = DbConnection.getInstance().getDBcon();
    }
    public ArrayList<Customer> getAllCustomers(String wClause){
        return miscWhere("");
    }
    //find customer by unique value
    public Customer getByName(String name){
        String wClause = "name = '"+ name +"'";
        return singleWhere(wClause);
    }
    public Customer getCustomerById(String id){
        String wClause = "id = '"+id+"'";
        return singleWhere(wClause);
    }
    public Customer getCustomerByPhone(String phone){
        String wClause = "phone = '"+phone+"'";
        return singleWhere(wClause);
    }
    public Customer getCustomerByEmail(String eMail){
        String wClause = "email = '"+eMail+"'";
        return singleWhere(wClause);
    }
    //insert new Customer
    public int insertCustomer(Customer cust){
        int result = -1;
		String query = "INSERT INTO customers(name, address, zip, phone, email, discount) VALUES"
				+ "('" + cust.getName() + "', '" + cust.getAddress() + "', '" + cust.getZip()
				+ "', '" + cust.getPhone() + "', '" + cust.getEmail() + "', '" + cust.getDiscount() + "')";
		System.out.println("insert: " + query);
		try {
			Statement stmt = db_con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Insert exception in customers db: " + ex);
		}
		return result;
    }
    //update informations for one Customer
    public int updateCustomer(Customer cust){
        int result = -1;

		String query="UPDATE customers SET ";
		if (cust.getName() != null) query += "name = '"+cust.getName()+"',";
		if (cust.getAddress() != null) query += "address = '"+cust.getAddress()+"',";
		if (cust.getZip() != null) query += "zip = '"+cust.getZip()+"',";
		if (cust.getPhone() != null) query += "phone = '"+cust.getPhone()+"',";
		if (cust.getEmail() != null) query += "email = '"+cust.getName()+"',";
                if (cust.getDiscount() != 0.0) query += "discount = '"+cust.getDiscount()+"',";
		if (query.endsWith(",")) query = query.substring(0, query.length()-1);
		query += " WHERE id = '"+ cust.getId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customers db: "+ex);
	  	}
		return result;
    }
    //delete Customer by Id
    public int deleteCustomer(String phone){
        int result = -1;

	  	String query = "DELETE FROM customers WHERE phone = '" +
				phone + "'";
                System.out.println("delete: " + query);
	  	try{ // delete from employees
	 		Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	result = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customers db: "+ex);
   	        }
		return result;

    }

    private ArrayList<Customer> miscWhere(String wClause){
            ResultSet results;
	    ArrayList<Customer> list = new ArrayList<Customer>();

	    String query =  buildQuery(wClause);

        try{ // read the employee from the database
			Statement stmt = db_con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			int snr=0;
			while( results.next() ){
				Customer custObj = new Customer();
				custObj = buildCustomer(results);
		        list.add(custObj);
			}//end while
			stmt.close();
		} catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
    }

    private Customer singleWhere(String wClause){
                ResultSet results;
		Customer custObj = new Customer();
                String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
				custObj = buildCustomer(results);
				stmt.close();
			}
		} catch(Exception e) {
	 		System.out.println("Query exception: " + e);
	 	}
		return custObj;
    }

    private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM customers";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

    private Customer buildCustomer(ResultSet results){
        Customer custObj = new Customer();
        try {
			custObj.setId(Integer.parseInt(results.getString(1)));
                        custObj.setName(results.getString(2));
                        custObj.setAddress(results.getString(3));
                        custObj.setZip(results.getString(4));
			custObj.setPhone(results.getString(5));
			custObj.setEmail(results.getString(6));
                        custObj.setDiscount(Double.parseDouble(results.getString(7)));
		} catch(Exception e) {
			System.out.println("error in building the customer object");
        }
		return custObj;
    }

}
